﻿Imports System.Data.SqlClient
Public Class FrmLapPhieuTraSoBoDiaChinh
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        comboboxHuyen.DisplayMember = "T_huyen"
        comboboxHuyen.ValueMember = "maH"
        comboboxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDMHienTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHienTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHienTrang.DisplayMember = "TenHienTrang"
        ComboBoxHienTrang.ValueMember = "MaHienTrang"
        ComboBoxHienTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiSoBo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMSoBo", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComBoBoxTenLoaiSo.DisplayMember = "TenSoBo"
        ComBoBoxTenLoaiSo.ValueMember = "maSoBo"
        ComBoBoxTenLoaiSo.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapHinhThuc()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHinhThuc", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHinhThuc.DisplayMember = "TenHinhThuc"
        ComboBoxHinhThuc.ValueMember = "maHinhThucID"
        ComboBoxHinhThuc.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub comboboxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles comboboxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & comboboxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewSoBoDiaChinh()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
           " Maso" &
           ", dc. NgayNhap" &
           ", dc.Quyenso" &
           ", dc.NamLap" &
           ", dc. Ghichu" &
           ", dc.CoSoPhapLy" &
           ", th.T_huyen" &
           ", tx.TenXa" &
           ", k.TenKho" &
           ", Ke.TenKe" &
           ", ht.TenHienTrang" &
           ", tli.TenLoaiTaiLieu" &
           ", sb.tensobo " &
           ", m.NgayMuon " &
           ", m.NguoiMuon " &
           ", m.DonViCongTac " &
           ", m.DiaChi " &
           ", m.MucDichMuon " &
           ", m. MasoMuonid " &
           " From  SoBoDiaChinh dc,  DMHienTrang ht, DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th, DMSoBo sb, SoBoDiaChinhMuon m " &
                                " WHERE " &
                                    " dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                    " and dc.makhoid = k.makhoid " &
                                    " and dc.maKeid = ke.makeid " &
                                    " and dc.MaHuyenId = '" & comboboxHuyen.SelectedValue.ToString & "' " &
                                    " and dc.mahuyenid = th.maH " &
                                    " and dc.MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                    " and dc.maxaid = tx.maxa " &
                                    " and dc.maLoaiSoboID = sb.masobo " &
                                    " and dc.maLoaisoboId = '" & ComBoBoxTenLoaiSo.SelectedValue.ToString & "' " &
                                    " and dc.maHienTrangID = ht.mahienTrang " &
                                    " and dc.masomuon = m.masomuonid " &
                                    " and dc.matinhtrangid = '2' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewSoBoDiaChinh.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub
    Public Sub NapDuLieuNguoiMuonVaodatagrid()
        DataGridViewTenNguoiMuon.Rows.Add(DateTimeNgayMuon.Text, DateTimeNgayTra.Text, TextBoxNguoiMuon.Text, TextBoxDonViMuon.Text, TextBoxDiaChi.Text, TextBoxMucDich.Text)
    End Sub
    Private Sub NapSoBoDiaChinhVaoTextBox()
        TextBoxMaSo.DataBindings.Clear()
        TextBoxMaSo.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Maso")
        TextBoxQuyenSo.DataBindings.Clear()
        TextBoxQuyenSo.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "Quyenso")
        TextBoxGhiChu.DataBindings.Clear()
        TextBoxGhiChu.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "GhiChu")
        TextBoxNamLap.DataBindings.Clear()
        TextBoxNamLap.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "NamLap")
        ComboBoxLoaiTaiLieu.DataBindings.Clear()
        ComboBoxLoaiTaiLieu.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "tenloaiTaiLieu")
        ComboBoxHienTrang.DataBindings.Clear()
        ComboBoxHienTrang.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "tenHientrang")
        TextBoxKho.DataBindings.Clear()
        TextBoxKho.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "tenKho")
        TextBoxKe.DataBindings.Clear()
        TextBoxKe.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "tenke")
        TextBoxNguoiMuon.DataBindings.Clear()
        TextBoxNguoiMuon.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "NguoiMuon")
        TextBoxDonViMuon.DataBindings.Clear()
        TextBoxDonViMuon.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "DonViCongTac")
        TextBoxDiaChi.DataBindings.Clear()
        TextBoxDiaChi.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "DiaChi")
        TextBoxMucDich.DataBindings.Clear()
        TextBoxMucDich.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "MucDichMuon")
        TextBoxMasoID.DataBindings.Clear()
        TextBoxMasoID.DataBindings.Add("Text", DataGridViewSoBoDiaChinh.DataSource, "masomuonid")
    End Sub
    Public Sub NapDuLieuNguoiMuon()
        DataGridViewTenNguoiMuon.Rows.Add(DateTimeNgayMuon.Text, TextBoxNguoiMuon.Text, TextBoxDonViMuon.Text, TextBoxDiaChi.Text, TextBoxMucDich.Text)
    End Sub

    Private Sub FrmTimKiemSoBoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' to mau cac dong trong datagridlietkebando doi mau khac nhau
        DataGridViewSoBoDiaChinh.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewSoBoDiaChinh.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewTenNguoiMuon.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewTenNguoiMuon.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        NapDonViHuyen()
        NapDMHienTrang()
        NapLoaiTaiLieu()
        NapLoaiSoBo()
        NapHinhThuc()
    End Sub
    Private Sub CapNhatDulieu()
        Try
            Dim Sql As String = ""
            Sql = " Update SoboDiaChinh Set " &
                                " MatinhtrangID  =  '1' " &
                                ", MaSoMuon = ' '" &
                                " where maso = '" & TextBoxMaSo.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub CapNhatDulieuMuon()
        Try
            Dim NgayTra As String
            NgayTra = String.Format("{0:MM/dd/yyyy}", DateTimeNgayTra.Value)
            Dim Sql As String = ""
            Sql = " Update SoboDiaChinhMuon Set " &
                                " MatinhtrangID  =  '1' " &
                                " ,NgayTra = '" & NgayTra & "'" &
                                " where MaSoMuonID = '" & TextBoxMasoID.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
   
    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        NapDataGridViewSoBoDiaChinh()
    End Sub
    Private Sub ButtonThoát_Click(sender As Object, e As EventArgs) Handles ButtonThoát.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewSoBoDiaChinh_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewSoBoDiaChinh.CellClick
        NapSoBoDiaChinhVaoTextBox()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        If TextBoxNguoiMuon.Text = "" Then
            MessageBox.Show(" Thông tin người mượn không được rỗng")
            Return
        End If
        If TextBoxDonViMuon.Text = "" Then
            MessageBox.Show("Đơn vị công tác không được rỗng")
            Return
        End If
        If TextBoxDiaChi.Text = "" Then
            MessageBox.Show("Địa chỉ không được rỗng")
            Return
        End If
        If TextBoxMucDich.Text = "" Then
            MessageBox.Show("Mục đích mượn không được rỗng")
            Return
        Else
            CapNhatDulieu()
            CapNhatDulieuMuon()
            NapDuLieuNguoiMuonVaodatagrid()
            NapDataGridViewSoBoDiaChinh()
        End If
    End Sub
    Private Sub DataGridViewSoBoDiaChinh_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewSoBoDiaChinh.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewSoBoDiaChinh.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewSoBoDiaChinh.RowHeadersWidth < Size.Width + 20 Then DataGridViewSoBoDiaChinh.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewTenNguoiMuon_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewTenNguoiMuon.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewTenNguoiMuon.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewTenNguoiMuon.RowHeadersWidth < Size.Width + 20 Then DataGridViewTenNguoiMuon.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))

    End Sub

    Private Sub DataGridViewSoBoDiaChinh_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewSoBoDiaChinh.CellContentClick

    End Sub
End Class
